<?php
    class Admincp_Model_Location extends Zend_Db_Table_Abstract
    {
        protected $_db;
        
        public function __construct()
        {
            $this->_db = Zend_Registry::get('db');
        }
        
        public function listLocation($locale)
        {
            $sql = $this->_db->select()
                            ->from('location AS ln')
                            ->join('location_language AS ll', 'ln.id = ll.location_id', array('name'))
                            ->join('language AS l', 'll.language_id = l.id', null)
                            ->where('l.locale = ?', $locale)
                            ->where('ln.parent <> 0')
                            ->order('ln.id DESC');
            return $sql;
        }
        
        public function listLocationChildren($locale)
        {
            $sql = $this->_db->select()
                            ->from('location AS ln', array('id'))
                            ->join('location_language AS ll', 'ln.id = ll.location_id', array('name'))
                            ->join('language AS l', 'll.language_id = l.id', null)
                            ->where('l.locale = ?', $locale)
                            ->where('ln.parent <> ?', 0)
                            ->order('ll.name ASC');
            $result = $this->_db->fetchAll($sql);
            return $result;
        }
        
        public function listLocationSelectbox($parent)
        {
            $sql = $this->_db->select()
                            ->from('location AS ln', array('id'))
                            ->join('location_language AS ll', 'ln.id = ll.location_id', array('name'))
                            ->join('language AS l', 'll.language_id = l.id', null)
                            ->where('l.locale = ?', 'vi')
                            ->where('ln.parent = ?', $parent)
                            ->order('ll.name ASC');
            $result = $this->_db->fetchAll($sql);
            return $result;
        }
        
        public function listForMenu($locale)
        {
            $sql = $this->_db->select()
                            ->from('location AS ln')
                            ->join('location_language AS ll', 'ln.id = ll.location_id', array('name'))
                            ->join('language AS l', 'll.language_id = l.id', null)
                            ->where('l.locale = ?', $locale)
                            ->order('ll.name ASC');
            $result = $this->_db->fetchAll($sql);
            return $result;
        }
        
        public function listMenu($parent, $menuData, $html = '') {
            $options = '';
            if(isset($menuData['parent'][$parent])) {
                foreach($menuData['parent'][$parent] as $value) {
                    if($menuData['items'][$value]['id'] == $menuData['currentParent']) {
                        $options .= '<option selected="selected" value="'.$menuData['items'][$value]['id'].'">'.$html.$menuData['items'][$value]['name'].'</option>';
                    }
                    else if($menuData['items'][$value]['id'] == $menuData['currentId']) {
                        $options .= '<option disabled="disabled" value="'.$menuData['items'][$value]['id'].'">'.$html.$menuData['items'][$value]['name'].'</option>';
                    }
                    else {
                        $options .= '<option value="'.$menuData['items'][$value]['id'].'">'.$html.$menuData['items'][$value]['name'].'</option>';
                    }
                    $options .= $this->listMenu($value, $menuData, $html.'----');
                }
            }
            return $options;
        }
        
        public function getLocationParent($parent, $locale)
        {
            $sql = $this->_db->select()
                            ->from('location AS ln', null)
                            ->join('location_language AS ll', 'ln.id = ll.location_id', array('name'))
                            ->join('language AS l', 'll.language_id = l.id', null)
                            ->where('l.locale = ?', $locale)
                            ->where('ln.id = ?', $parent);
            $result = $this->_db->fetchOne($sql);
            return $result;
        }
        
        public function getLocationById($id)
        {
            $sql = $this->_db->select()
                            ->from('location_language AS ll', array('name'))
                            ->join('language AS l', 'll.language_id = l.id', array('locale'))
                            ->where('ll.location_id = ?', $id);
            $result = $this->_db->fetchAll($sql);
            return $result;
        }
        
        public function getLocationNameById($id)
        {
            $sql = $this->_db->select()
                            ->from('location_language AS ll', array('name'))
                            ->join('language AS l', 'll.language_id = l.id', null)
                            ->where('ll.location_id = ?', $id)
                            ->where('l.locale = ?', 'vi');
            $result = $this->_db->fetchOne($sql);
            return $result;
        }
        
        public function getLocationByLocale($id, $locale)
        {
            $sql = $this->_db->select()
                            ->from('location AS lc', array('parent'))
                            ->join('location_language AS ll', 'lc.id = ll.location_id', array('name'))
                            ->join('language AS l', 'll.language_id = l.id', null)
                            ->where('l.locale = ?', $locale)
                            ->where('lc.id = ?', $id);
            $result = $this->_db->fetchRow($sql);
            return $result;
        }
        
        public function checkLocation($name, $id = null)
        {
            $sql = $this->_db->select()
                            ->from('location_language')
                            ->where('name = ?', $name);
            if($id != null) {
                $sql = $sql->where('location_id <> ?', $id);
            }
            $result = $this->_db->fetchAll($sql);
            return count($result);
        }
        
        public function addLocation($data)
        {
            $this->_db->insert('location', $data);
            return $this->_db->lastInsertId();
        }
        
        public function addLocationLanguage($data)
        {
            $this->_db->insert('location_language', $data);
        }
        
        public function updateLocation($data, $id)
        {
            $where = $this->getAdapter()->quoteInto('id = ?', $id);
            $this->_db->update('location', $data, $where);
        }
        
        public function updateLocationLanguage($data,$id)
        {
            $where = 'location_id = '.$id.' AND language_id = '.$data['language_id'];
            $this->_db->update('location_language', $data, $where);
        }
        
        public function deleteLocation($id)
        {
            $where = $this->getAdapter()->quoteInto('id = ?', $id);
            $this->_db->delete('location', $where);
            $where = $this->getAdapter()->quoteInto('location_id = ?', $id);
            $this->_db->delete('location_language', $where);
            $this->deleteChild($id);
        }
        
        public function deleteChild($id)
        {
            $sql = $this->_db->select()
                            ->from('location', array('id'))
                            ->where('parent = ?', $id);
            $result = $this->_db->fetchAll($sql);
            if(count($result) > 0) {
                foreach($result as $value) {
                    $where = $this->getAdapter()->quoteInto('id = ?', $value['id']);
                    $this->_db->delete('location', $where);
                    $where = $this->getAdapter()->quoteInto('location_id = ?', $value['id']);
                    $resultDelete = $this->_db->delete('location_language', $where);
                    if($resultDelete > 0) {
                        $this->deleteChild($value['id']);
                    }
                }
            }
        }
        
        public function deleteMultiLocation($listId)
        {
            foreach($listId as $id) {
                $this->deleteLocation($id);
            }
        }
        
        public function searchLocation($data, $locale)
        {
            $sql = $this->_db->select()
                            ->from('location AS ln')
                            ->join('location_language AS ll', 'ln.id = ll.location_id', array('name'))
                            ->join('language AS l', 'll.language_id = l.id', null)
                            ->where('ln.parent <> 0')
                            ->where('l.locale = ?', $locale);
            if($data['name'] != '') {
		        $sql = $sql->where('ll.name_plaintext LIKE ?', $data['name']);
		    }
		    if($data['status'] != 0) {
		        $sql = $sql->where('ln.status = ?', $data['status']);
		    }
		    return $sql;
        }
    }
